Hello everyone.
In this lecture we will cover some of the most important actual functions textual functions are used
to perform operations on the text type of data.
For example if we need to remove any particular word from a group of words or if we need to extract
first five or last five characters of any word for such operations we need to use textual functions.
So let's get started with these first textual function here.
You can see we have student 1 pre interred in the first column and you can notice that there are unwanted
extra spaces in front of it
similarly.
The second and third strand one values have extra spaces either in between or in the end of the text
string
to correct this.
Either we can edit each one of these text strings or we can use a simple trim function trim function
removes extra spaces from the text string it will remove spaces from the beginning or at the end or
even between two words.
Also let's look at our example in this cell.
There are extra spaces in the front of string STUDENT 1 using trim.
We will remove these excess spaces using trim function is very similar to using other mathematical formulas
first to start the function.
We have to type is equal to then we will maintain the function name that is trim and after that we will
mention the text string.
We want to create
and presented.
You can see extra spaces are removed.
Now let's move on to the next example.
You can see extra spaces between student and the numeric one again we will use trim function to remove
these extra spaces.
We will follow the same steps
and now you can see that there is only one space between the word student and numeric character one
frame function has removed the excess spaces which will present between these two words now for the
third row.
You can see that there are extra spaces at the end of the string.
Again we use trim function.
We can simply drag the function from a low
and now the extra spaces at the end are to move however these cells contain formula instead of the required
string to get string.
We know what we have to do.
We use the copy and paste as values technique
these cells now have actual string values with no extra spaces.
Now let's move on to the next function
suppose I have two strings and two cells.
Let's say first cell contains first name and second cell contains last name.
Now how do we combine these two strings to create a full for such situations.
We can use concatenate function to combine the two strings let us first try to combine English math
and science as always we will start our formula with equal to symbol.
Then with eight concatenate
you can see that in this cell we have all the three strings joined together.
First comes maths then without any space comes the next train which is signed.
And then comes the third string which is English
to further understand the use of this function.
Let us try to create a table where instead of just marks we will write that student 1 has called so
many marks in this subject.
To do this we go to the cell and type concatenate
select student one cell comma write the script between double quotation marks
space has called Space
comma.
Select the marks from Table 1
comma no again into the script in double quotation marks space in
maths
when I press enter.
You can see that the the complete sentence is created.
STUDENT 1 has scored 95 in maths.
You can see in the formula bar concatenate function is taking student 1 from this say has scored is
an input by US marks is coming from the upper table and then there's the string which is an input from
our side.
Now to extend this for other student you can simply double click on the bottom right corner and the
entire column will reflect.
You can see for student 5 also the entire string is correct.
STUDENT 5 has called fortify in maths
concatenation can also be done using Ampersand symbol ampersand is the symbol that we see on the numeric
key 7 on our keyboard and it looks like this.
This is also the end symbol that we use while writing text.
So now in the English column we will apply the ampersand symbol to get this same design.
To do this select student one cell
type and pushing
into the string within double quotation marks as called
Ampersand again.
Marks
within w gradation marks in English
you can see that ampersand performs the same operation as concatenate function in concatenation it's
like a normal function when you first mentioned the function and then give all the strings as parameters
in inputting it is like using plus symbol you write the string at an imposing you write another string
at an imposing write into the string and so on.
Next up is substitute function it is used to substitute a particular string with another string in the
selected text for example to rectify this spelling of students to rectify the spelling of student v
type is equal to substitute
selected text in which you want to change the strings comma input the part of the string that you want
to replace.
Always remember to put double quotation marks while maintaining strings
we want to change b to the.
So the third parameter is what do you want it to be changed
you can see the text to bend is no student.
Same thing can be done in sentences also for second row of the table.
We can update this code in the sentence from thirty eight to ninety five.
So student 1 scored 95 marks in St.
in the third row.
We can change the text science to English.
The important thing to note here is the word science is case sensitive.
If you make the errors of science small it will not be able to recognize the same.
You can also note in the formula column we have written the formulas but these formulas are not returning
the values in this cell.
You can still see the formula written to do this.
We have used a single space in front of the equal to symbol.
It is because of this base.
Excel is not calculating the actual value of this formula and showing this whole thing as a text in
this cells.
If I remove this space and enter it you can see that the formula will be calculated and the value of
formula will be shown.
So in this way if you want to show the formula to someone without calculating the value you can just
add a single space in front of the equal to symbol
next.
Is the upper and lower function these functions simply convert the entire input string to upper or lowercase
for this which type is equal to about
intellect.
This string
you can see that student 1 string is now completely in uppercase in the next cell.
We will use lower
and you can notice that the s of the student which was earlier uppercase is no lowercase
to completely fill the table.
We select both the cells go to the bottom right corner and double click and the entire table is filled
after this.
We look at the length function length function returns the length of the input string that is it returns
the count of characters including spaces in the string.
So for example in the fossil we have student wondered in student contained seven characters.
Then there is a space and then there is a numeric one total it contains nine characters so we will now
use the length function to see how many characters does this actually help
Lin and then we select this string.
You can see that this Lent function returns the number of characters including the space in between
we can extend it for the other strings.
You can see that the next during Lent is eleven.
You can manually check if Excel is giving us the correct answers the last set of functions we are going
to discuss.
Left right and made left function will help us select a particular count of characters in the string
from the left in this table.
We have Q What code values which consist of three different parts.
One is the ear of manufacturing which are the first four Connectors of the Q are called the last two
characters are the group of that product and in-between there are five connectors which represent the
product Bailey of that particular product to segregate these three parts from the given QR code.
We first use the left function to apply left function.
We start typing left
select the string comma type for
this will return the first four characters of the selected string.
You can see that the first four strings.
That is 2 0 1 8 is written by the left.
Function right does the same thing.
It just starts the counting from the end of the string.
For example here we want to get the right two characters of the string so we vibrate.
We select the string and then type two.
You can see the right two characters have indeed done
made is like left.
Only differences that you can change the starting point for the count.
For example if you want the fifth to nine characters from this QR code you type mid
select the string give the starting point which is the fifth character and then give the number of characters
here we want five characters
you can see that the middle 5 characters are returned by this made function we can select these three
sets and double click on the bottom right corner to complete this they will
like I told you in the previous lecture all the textual functions can also be found in the formulas
tab of the Excel menu
I would recommend that you go and explore these other actual functions.
Also that's all for this lecture.
See you in the next one.